# -*- coding: utf-8 -*-#
#-------------------------------------------------------------------------------
# 建立者:        潘炎珍  
# Name:         hw02
# Description:
# Author:       98745
# Date:         2019/4/20
#-------------------------------------------------------------------------------
import json
from pymysql import *

class Analyze:
    #将json字符串转为字典的形式
    def get_dict(self):
        try:
            f=open('./file.json','r',encoding='utf-8')
            jsonstr=f.read()
            dict=json.loads(jsonstr)#将json字符串转换为字典
            # print(dict)
            f.close()
            return dict
        except Exception as e:
            print(e)

    #获取字典中的thd_name数据
    def get_thd_name(self,dict):
        try:
            for item in dict['data']:
                thd_name=item['thd_name']
                self.get_product(thd_name,item['product'])
        except Exception as e:
            print(e)

    #获取每种thd_name的product，并将其插入数据库
    def get_product(self,thd_name,product):
        try:
            for item in product:
                id=item['id']
                title=item['title']
                t_id=item['t_id']
                orderno=item['orderno']
                data=[thd_name,id,title,t_id,orderno]
                db=self.dbconnect()
                self.insert(db,data)
        except Exception as e:
            print(e)

    #获取数据库连接
    def dbconnect(self):
        try:
            db=connect('localhost','root','1997','python',charset='utf8')
            return db
        except Exception as e:
            print(e)

    #建立表
    def createtable(self,db):
        try:
            cursor=db.cursor()
            sql='''create table json(thd_name varchar(10),id int,title varchar(10),t_id int,orderno int);'''
            cursor.execute(sql)
            db.commit()
        except Exception as e:
            print(e)
            db.rollback()

    #将数据插入数据库
    def insert(self,db,data):
        try:
            cursor = db.cursor()
            sql="insert into json values('{}',{},'{}',{},{})".format(data[0],int(data[1]),data[2],int(data[3]),int(data[4]))
            cursor.execute(sql)
            db.commit()
        except Exception as e:
            print(e)
            db.rollback()


if __name__=="__main__":
    try:
        s=Analyze()
        db=s.dbconnect()
        s.createtable(db)
        dict=s.get_dict()
        s.get_thd_name(dict)
    except Exception as e:
        print(e)
